package edu.cibertec.alquiler.dao.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;

import util.MySqlDBConn;

import edu.cibertec.alquiler.bean.CarritoBean;
import edu.cibertec.alquiler.bean.VideoBean;
import edu.cibertec.alquiler.dao.VideoDAO;
import edu.cibertec.alquiler.entity.DetalleUsuarioVideo;
import edu.cibertec.alquiler.entity.Video;

public class VideoJPADAO implements VideoDAO {
	
	private EntityManagerFactory emf;
	private EntityManager em;
	
	
	public void setEntityManagerFactory(EntityManagerFactory emf){
			this.emf=emf;
		}

	@SuppressWarnings("unchecked")
	@Override
	public List<Video> listarVideos() throws Exception {
		em=emf.createEntityManager();
		List<Video> listaVideos=new ArrayList<Video>();
		try {
			Query q = em.createQuery("Select v from Video v");
			listaVideos= q.getResultList();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally{
			//em.clear();
			em.close();
		}
		return listaVideos;
	}

	@Override
	public void insertarNuevoVideo(Video bean) throws Exception {
		em=emf.createEntityManager();
		try {
			em.getTransaction().begin();
			em.persist(bean);
			em.flush();
			em.getTransaction().commit();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			em.getTransaction().rollback();
		}finally{
			em.clear();
			em.close();
		}
	}

	@Override
	public void nuevoPrestamo(DetalleUsuarioVideo bean) throws Exception {
		em= emf.createEntityManager();
		try {
			em.getTransaction();
			em.persist(bean);
			em.flush();
			em.getTransaction().commit();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally{
			em.clear();
			em.close();
		}
	}

	@Override
	public Video buscarVideo(Video bean) throws Exception {
		em=emf.createEntityManager();
		Video entidad=null;
		try {
			entidad= new Video();
			 entidad= em.find(Video.class, bean.getCodVideo());
			 
			System.out.println("EN MI BUSCAR EH CAPTURADO EL VIDEO");
			System.out.println("CODIGO : " + entidad.getCodVideo());
			System.out.println("TITULO : " + entidad.getTitulo());
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally{
			//em.clear();
			em.close();
		}
		return entidad;
	}

	@Override
	public void actualizarVideo(Video bean) throws Exception {
		em=emf.createEntityManager();
		Video video=null;
		try {
			em.getTransaction().begin();
			video=em.find(Video.class, bean.getCodVideo());
			
			video.setAnio(bean.getAnio());
			video.setDirector(bean.getDirector());
			video.setDuracion(bean.getDuracion());
			video.setPrecio(bean.getPrecio());
			video.setProtagonista(bean.getProtagonista());
			video.setSinopsis(bean.getSinopsis());
			video.setStock(bean.getStock());
			video.setTbCategoria(bean.getTbCategoria());
			video.setTbEstado(bean.getTbEstado());
			video.setTbGenero(bean.getTbGenero());
			video.setTitulo(bean.getTitulo());
			video.setUrlImagen(bean.getUrlImagen());
			video.setUrlTrailer(bean.getUrlTrailer());
			em.merge(video);
			em.flush();
			em.getTransaction().commit();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally{
			//em.clear();
			em.close();
		}
	}

	@Override
	public void eliminarVideo(Video bean) throws Exception {
		em=emf.createEntityManager();
		Video video=null;
		try {
			em.getTransaction().begin();
			video=em.find(Video.class, bean.getCodVideo());
			em.remove(video);
			em.flush();
			em.getTransaction().commit();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally{
			em.close();
		}
		
		
	}

	@Override
	public ArrayList<VideoBean> getVideos() throws Exception {
ArrayList<VideoBean> videos = new ArrayList<VideoBean>();
		
		String sql = "SELECT * FROM tb_video v inner join tb_genero g on g.cod_genero = v.cod_genero inner join tb_categoria c on c.cod_Categoria = v.cod_categoria";
		System.out.println("SENTENCIA SQL : " + sql);
		
		Connection conn = new MySqlDBConn().getConnection();
		Statement stm = conn.createStatement();
		System.out.println("query : "+sql);
		ResultSet rst = stm.executeQuery(sql);
		while (rst.next()){
			VideoBean b = new VideoBean();
			b.setCodVideo(rst.getInt(1));
			b.setTitulo(rst.getString(2));
			b.setGenero(rst.getString(3));
			b.setPrecio(rst.getDouble(4));
			b.setDuracion(rst.getString(5));
			b.setDirector(rst.getString(6));
			b.setProtagonista(rst.getString(7));
			b.setAnio(rst.getString(8));
			b.setSinopsis(rst.getString(9));
			b.setUrlImagen(rst.getString(10));
			b.setUrlTrailer(rst.getString(11));
			b.setStock(rst.getInt(14));
			b.setGenero(rst.getString(16));
			b.setCategoria(rst.getString(18));
			videos.add(b);
			
			
		}
		rst.close();
		stm.close();
		conn.close();

		return videos;
	}

	@Override
	public VideoBean getVideoByID(String id) throws Exception {
	String sql = "SELECT * FROM tb_video v inner join tb_genero g on g.cod_genero = v.cod_genero inner join tb_categoria c on c.cod_Categoria = v.cod_categoria where v.cod_video ="+id;
	VideoBean b = new VideoBean();
	
		
		Connection conn = new MySqlDBConn().getConnection();
		Statement stm = conn.createStatement();
		System.out.println("query : "+sql);
		ResultSet rst = stm.executeQuery(sql);
		if (rst.next()){
			
			b.setCodVideo(rst.getInt(1));
			b.setTitulo(rst.getString(2));
			b.setGenero(rst.getString(3));
			b.setPrecio(rst.getDouble(4));
			b.setDuracion(rst.getString(5));
			b.setDirector(rst.getString(6));
			b.setProtagonista(rst.getString(7));
			b.setAnio(rst.getString(8));
			b.setSinopsis(rst.getString(9));
			b.setUrlImagen(rst.getString(10));
			b.setUrlTrailer(rst.getString(11));
			b.setStock(rst.getInt(14));
			b.setGenero(rst.getString(16));
			b.setCategoria(rst.getString(18));

		}
		rst.close();
		stm.close();
		conn.close();

		return b;
		
	}

	@Override
	public ArrayList<VideoBean> getVideosFiltrados(VideoBean filtro)
			throws Exception {
		ArrayList<VideoBean> videos = new ArrayList<VideoBean>();
		String sql = "SELECT * FROM tb_video v inner join tb_genero g on g.cod_genero = v.cod_genero inner join tb_categoria c on c.cod_Categoria = v.cod_categoria where v.stock>0";
		
		if(filtro.getTitulo()!=null){
			sql+=" and v.titulo like '%"+filtro.getTitulo()+"%' or v.protagonista like '%"+filtro.getTitulo()+"%' or v.director like '%"+filtro.getTitulo()+"%'";
		}
		
		Connection conn = new MySqlDBConn().getConnection();
		Statement stm = conn.createStatement();
		System.out.println("query : "+sql);
		ResultSet rst = stm.executeQuery(sql);
		while (rst.next()){
			VideoBean b = new VideoBean();
			b.setCodVideo(rst.getInt(1));
			b.setTitulo(rst.getString(2));
			b.setGenero(rst.getString(3));
			b.setPrecio(rst.getDouble(4));
			b.setDuracion(rst.getString(5));
			b.setDirector(rst.getString(6));
			b.setProtagonista(rst.getString(7));
			b.setAnio(rst.getString(8));
			b.setSinopsis(rst.getString(9));
			b.setUrlImagen(rst.getString(10));
			b.setUrlTrailer(rst.getString(11));
			b.setStock(rst.getInt(14));
			b.setGenero(rst.getString(16));
			b.setCategoria(rst.getString(18));
			videos.add(b);
			
			
		}
		rst.close();
		stm.close();
		conn.close();
		
		
		
		
		
		return videos;
		
	
	}
	
	public void RealizaCompra(ArrayList<CarritoBean> carrito) throws Exception{
		
		
		Connection conn = new MySqlDBConn().getConnection();
	

		
		for(int i=0;i<carrito.size();i++){
		 CarritoBean obj = new CarritoBean();
		 obj = carrito.get(i);
		 int cantidad = 0;
	
	
			
		
		
		
		
		
		 
		 
		 
		
		String sql = "insert into tb_detalle_usuario_video values(null,?,?,?,curDate(),4,0)";
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, obj.getCodUsuario());
		st.setString(2, obj.getCodVideo());
		st.setString(3, obj.getDias());
		
		st.executeUpdate();
		
		String sql2 = "SELECT stock from tb_video where cod_video = ?";
		st = conn.prepareStatement(sql2);
		st.setString(1, obj.getCodVideo());
		ResultSet rs = st.executeQuery();
			if(rs.next()){
				cantidad = rs.getInt(1);
			}

			String sql3 = "update  tb_video set stock = ? where cod_video = ?";
			st = conn.prepareStatement(sql3);
			st.setInt(1,cantidad-1);
			st.setString(2, obj.getCodVideo());
			
			st.executeUpdate();
	
		}

		conn.close();
	}
	
		}
		
		
	


	


